﻿--
-- 棋牌游戏sql脚本(postgresql)
-- huangtao117@gmail.com
--
-- 1.安装好postgresql后建立demo数据库
-- createdb demo
-- 2.命令行管理工具连接demo
-- psql demo
-- 3.导入脚本
-- /i demo_postgresql.sql
-- 4.导入成功后可以查看表和函数
-- /df或者/dt
-- 5.退出
-- /q
--

--
-- 表
--

-- 全局变量表
CREATE TABLE IF NOT EXISTS t_gobal(
    id serial PRIMARY KEY,      	-- 编号
    g_name text,                	-- 名字
    val_type int,              		-- 值类型(0-文本,1-数字)
    g_text text,               		-- 文本值
    g_int int                 		-- 数字值
);
insert into t_gobal(g_name,val_type,g_text,g_int) values('guest_index', 1, '', 10000);

-- 用户表
CREATE TABLE IF NOT EXISTS t_user(
    userid text PRIMARY KEY,        -- 用户ID
    id_type text,                   -- 帐号类型(默认,游客,QQ,新浪微博...)
    userpwd text,                   -- 密码
    temppwd text,                   -- 临时密码
    sex smallint DEFAULT 0,         -- 性别(0女;1男)
    paypoint bigint DEFAULT 0,      -- 支付点(虚拟币)(一般等值于货币)
    play_exp bigint DEFAULT 0,      -- 经验值
    gold bigint DEFAULT 0,          -- 财富(银子,金币等)
    box_gold bigint DEFAULT 0,      -- 保险箱财富
    score bigint DEFAULT 0,         -- 积分
    total_games bigint DEFAULT 0,   -- 游戏对局数
    realname text,                  -- 真实姓名
    identitycard text,              -- 身份证
    telphone text,                  -- 手机号码
    address text,                   -- 快递地址
    flag_forbid int DEFAULT 0,      -- 是否冻结
    flag_admin int DEFAULT 0,       -- 是否管理员
    vip_level int DEFAULT 0,        -- vip等级
    group_id int DEFAULT 0,         -- 社团(团队,战队)ID
    ip_reg text,                    -- 注册IP
    ip_login text,                  -- 最后登录IP
    dev_reg text,                   -- 注册设备(如pc的机器码)
    dev_login text,                 -- 登录设备(如pc的机器码)
    dev_bind text,                  -- 绑定设备
    time_reg timestamp,             -- 注册时间戳
    time_login timestamp            -- 最后登录时间戳
);

-- 保留关键词
CREATE TABLE IF NOT EXISTS t_reserve(
    id serial PRIMARY KEY,      -- 编号
    rsv text                    -- 文本
);
insert into t_reserve(rsv) values('.');
insert into t_reserve(rsv) values('_');
insert into t_reserve(rsv) values('GM');
insert into t_reserve(rsv) values('GUEST');
insert into t_reserve(rsv) values('QQ');

-- 禁止ip地址表
CREATE TABLE IF NOT EXISTS t_forbidip(
    id serial PRIMARY KEY,      -- 编号
    ip text                     -- ip地址192.168.0.*
);

-- 禁止设备表
CREATE TABLE IF NOT EXISTS t_forbiddev(
    id serial PRIMARY KEY,      -- 编号
    device text,                -- 设备标示(网卡号、手机卡序列号)
    devtype text                -- 设备类型
);

-- 登录日志表
CREATE TABLE IF NOT EXISTS log_login(
    id serial PRIMARY KEY,      -- 编号
    userid text,                -- 用户ID
    ip_login text,              -- 登录IP
    app_login text,             -- 登录应用
    dev_login text,             -- 登录设备
    time_login timestamp        -- 登录时间
);

-- 兑换日志表
CREATE TABLE IF NOT EXISTS log_buy(
    id serial PRIMARY KEY,      -- 编号
    userid text,                -- 用户ID
    buyid int,                  -- 购买物品ID
    paypoint bigint,            -- 使用支付点
    point_new bigint,           -- 原有支付点
    point_old bigint,           -- 新支付点
    score_new bigint,           -- 原财富积分
    score_old bigint,           -- 新财富积分
    time_buy timestamp
);

-- 记分日志表
CREATE TABLE IF NOT EXISTS log_score(
    id serial PRIMARY KEY,      -- 编号
    userid text,                -- 用户ID
    old_score bigint,           -- 原积分
    new_score bigint,           -- 新积分
    pay int DEFAULT 0,          -- 服务费
    vs_info text,               -- 对局信息
    time_score timestamp        -- 记分时间
);

-- 充值日志
CREATE TABLE IF NOT EXISTS log_pay(
    id serial PRIMARY KEY,      -- 编号
    userid text,                -- 用户ID
    pay_mode text,              -- 支付途径
    pay_tid text,               -- 交易号(卡号)
    pay_money bigint,           -- 充值人民币
    point_new bigint,           -- 新支付点
    point_old bigint,           -- 旧支付点
    score_new bigint,           -- 新财富点
    score_old bigint,           -- 旧财富点
    time_pay timestamp
);


-------------------------------------------------------------------------------
--
-- 存储过程
--

--
-- 测试
--
CREATE OR REPLACE FUNCTION cf_test(a integer, b integer) RETURNS integer AS $$
DECLARE
    s integer := 0;
BEGIN
    s := a + b;
    RETURN s;
END;
$$ LANGUAGE plpgsql;

--
-- 禁止IP地址判定
--
CREATE OR REPLACE FUNCTION cf_forbidip(checkip text) RETURNS integer AS $$
DECLARE
    ipstr text;
    s1 text;
    s2 text;
    s3 text;
BEGIN
    --raise info 'mydebuginfo';

    s1 = split_part(checkip, '.', 1);
    ipstr = s1 || '.*.*.*';
    PERFORM * FROM t_forbidip WHERE ip=ipstr;
    IF FOUND THEN
        RETURN 1;
    END IF;

    s2 = split_part(checkip, '.', 2);
    ipstr = s1 || '.' || s2 || '.*.*';
    PERFORM * FROM t_forbidip WHERE ip=ipstr;
    IF FOUND THEN
        RETURN 1;
    END IF;

    s3 = split_part(checkip, '.', 3);
    ipstr = s1 || '.' || s2 || '.' || s3 || '.*';
    PERFORM * FROM t_forbidip WHERE ip=ipstr;
    IF FOUND THEN
        RETURN 1;
    END IF;

    PERFORM * FROM t_forbidip WHERE ip=checkip;
    IF FOUND THEN
        RETURN 1;
    END IF;

    RETURN 0;
END;
$$ LANGUAGE plpgsql;

--
-- 注册
--
CREATE OR REPLACE FUNCTION cf_register(uid text,pwd text,
    sex integer,ip text,sn text,real_name text,
    idcard text,m_phone text) RETURNS integer AS $$
DECLARE
    ret integer := 0;
BEGIN
    -- 1 成功
    -- 0 失败
    -- -1 禁止的IP地址
    -- -2 ID已经被注册

    -- 是否禁止IP地址
    ret := cf_forbidip(ip);
    --raise info 'fobidip:%', ret;
    IF ret > 0 THEN
        ret := -1;
        RETURN ret;
    END IF;

    -- 是否是禁止的设备

    --RAISE NOTICE 'userid is %', $1;
    -- 检查帐号是否已经存在
    PERFORM * FROM t_user WHERE userid=$1;
    --GET DIAGNOSTICS rows = ROW_COUNT;
    --RAISE NOTICE 'rows is %', rows;
    IF FOUND THEN
        ret := -2;
        RETURN ret;
    END IF;

    -- 插入记录
    INSERT INTO t_user(userid,id_type,userpwd,sex,realname,
        identitycard,telphone,ip_reg,dev_reg,time_reg)
        VALUES($1,'0',$2,$3,$6,$7,$8,$4,$5,now());

    ret := 1;
    RETURN ret;
END;
$$ LANGUAGE plpgsql;

--
-- 登录
-- b_guest:是否游客账号
--
CREATE OR REPLACE FUNCTION cf_login(uid text,pwd text,
    ip text,sn text,tel text,b_guest integer)
    RETURNS TABLE(
        result int,
        userid text,
        sex int,
        paypoint bigint,
        gold bigint,
        boxgold bigint,
        tgames bigint,
        vip int,
        ggroup int
    ) AS $$
DECLARE
    iGuest integer := 0;
    iLen integer := 0;
    result integer := 0;
    dbpwd text;
    time_month1 timestamp;
    r_uid text;
    r_sex integer := 0;
    r_vip integer := 0;
    r_group integer := 0;
    r_paypoint bigint := 0;
    r_gold bigint := 0;
    r_boxgold bigint := 0;
    r_tgames bigint := 0;
    rstext text;
BEGIN
    -- result:
    -- 1 成功
    -- 0 失败
    -- -1 禁止的IP地址
    -- -2 没有这个ID
    -- -3 密码错误
    -- -4 ID已冻结
    -- -5 参数错误

    -- 是否禁止IP地址
    result := cf_forbidip(ip);
    --raise info 'fobidip:%', result;
    IF result > 0 THEN
        result := -1;
        RETURN QUERY SELECT result;
    END IF;

    -- 是否游客登录
    iLen = length(uid);
    IF b_guest > 0 THEN
        IF iLen = 0 THEN
            -- 获得一个游客帐号
            -- 从游客表中获取3个月没有登陆的账号
            time_month1 = now() - interval '3 month';
            SELECT guestid INTO uid FROM t_guest WHERE time_login < time_month1 LIMIT 1;
            IF NOT FOUND THEN
                iGuest := 0;
                SELECT g_int INTO iGuest FROM t_gobal WHERE g_name='guest_index';
                r_uid := 'GUEST_' || g_int;
                INSERT INTO t_guest(guestid,ip_login,sn_login,telphone,time_login)
                    VALUES(r_uid,$3,$4,$5,now());
            ELSE
            	UPDATE t_guest SET ip_login=$3,sn_login=$4,telphone=$5,time_login=now() WHERE guestid=$1;
            	r_uid := $1;
            END IF;
        ELSE
        	UPDATE t_guest SET ip_login=$3,sn_login=$4,telphone=$5,time_login=now() WHERE guestid=$1;
        	r_uid := $1;
        END IF;
        r_paypoint := 0;
        r_boxgold := 0;
        r_vip := 0;
        r_group := 0;
        SELECT sex,gold,total_games INTO r_sex,r_gold,r_tgames FROM t_guest WHERE guestid=r_uid;
    ELSE
        IF iLen = 0 THEN
            result := -5;
            RETURN QUERY SELECT result;
        END IF;

	    --RAISE NOTICE 'userid is %', $1;
   		-- 检查帐号是否已经存在
    	PERFORM * FROM t_user WHERE userid=$1;
    	--GET DIAGNOSTICS rows = ROW_COUNT;
    	--RAISE NOTICE 'rows is %', rows;
    	IF NOT FOUND THEN
        	result := -2;
        	RETURN QUERY SELECT result;
    	END IF;

	    -- 是否冻结帐号
    	SELECT flag_forbid INTO result FROM t_user WHERE userid=$1;
    	IF result <> 0 THEN
        	result := -4;
        	RETURN QUERY SELECT result;
    	END IF;

    	-- 检查密码
    	SELECT userpwd INTO dbpwd FROM t_user WHERE userid=$1;
    	IF dbpwd <> $2 THEN
        	result := -3;
        	RETURN QUERY SELECT result;
    	END IF;

    	r_uid := $1;
    	-- 获取用户信息
    	SELECT sex,paypoint,gold,box_gold,total_games,vip_level,group_id INTO
        	r_sex,r_paypoint,r_gold,r_boxgold,r_tgames,r_vip,r_group
            FROM t_user WHERE userid=r_uid;
    	UPDATE t_user SET ip_login=$3,time_login=now() WHERE userid=r_uid;
    END IF;

    -- 插入登录记录
    INSERT INTO log_login(userid,ip_login,dev_login,time_login)
        VALUES(r_uid,$3,$4,now());

    result := 1;
    RETURN QUERY SELECT result,r_uid,r_sex,r_paypoint,r_gold,
        r_boxgold,r_tgames,r_vip,r_group;
END;
$$ LANGUAGE plpgsql;

--
-- 2人记分
--
CREATE OR REPLACE FUNCTION cf_winlost2(appid text, uid1 text, gold_val1 integer,
    score1_val integer, uid2 text, gold_val2 integer, score2_val integer,
    flag integer, gtime integer)
    RETURNS TABLE(
        result integer,
        userid1 text,
        gold1 bigint,
        score1 bigint,
        userid2 text,
        gold2 bigint,
        score2 bigint
    ) AS $$
DECLARE
    result integer := 0;
    temp bigint := 0;
    fwf int := 0;
    old_gold1 bigint := 0;
    old_score1 bigint := 0;
    old_gold2 bigint := 0;
    old_score2 bigint := 0;
    new_gold1 bigint := 0;
    new_score1 bigint := 0;
    new_gold2 bigint := 0;
    new_score2 bigint := 0;
BEGIN
    -- result:
    -- 1 成功
    -- 0 失败
    -- -1 不够输
    -- -2 没有这个ID
    -- -3 赢>输
    -- -4 输赢太大

    --RAISE NOTICE 'userid is %', $1;
    -- 检查帐号是否已经存在
    PERFORM * FROM t_user WHERE userid=$1;
    --GET DIAGNOSTICS rows = ROW_COUNT;
    --RAISE NOTICE 'rows is %', rows;
    IF NOT FOUND THEN
        result := -2;
        RETURN QUERY SELECT result;
    END IF;
    PERFORM * FROM t_user WHERE userid=$2;
    IF NOT FOUND THEN
        result := -2;
        RETURN QUERY SELECT result;
    END IF;

    -- 银子是否平衡
    IF gold_val1 > 0 AND gold_val2 > 0 THEN
        result := -3;
        RETURN QUERY SELECT result;
    END IF;
    temp := gold_val1 + gold_val2;
    IF temp < 0 THEN
        result := -3;
        RETURN QUERY SELECT result;
    END IF;

    -- 计算服务费
    fwf = abs(abs(gold_val1) - abs(gold_val2));

    -- 获取用户信息
    SELECT gold,score INTO old_gold1, old_score1 FROM t_user WHERE userid=$2;
    SELECT gold,score INTO old_gold2, old_score2 FROM t_user WHERE userid=$5;

    -- 不够输
    IF $3 < 0 AND old_gold1 < abs($3) THEN
        result := -1;
        RETURN QUERY SELECT result;
    END IF;
    IF $6 < 0 AND old_gold2 < abs($6) THEN
        result := -1;
        RETURN QUERY SELECT result;
    END IF;

    IF $3 > 1000000000 OR $3 < -1000000000 THEN
        result := -4;
        RETURN QUERY SELECT result;
    END IF;

    UPDATE t_user SET gold=gold+$3,score=score+$4 WHERE userid=$1;
    UPDATE t_user SET gold=gold+$6,score=score+$7 WHERE userid=$5;
    new_gold1 = old_gold1 + $3;
    new_score1 = old_score1 + $4;
    new_gold2 = old_gold2 + $6;
    new_score2 = old_score2 + $7;

    -- 插入记分日志
    IF $3 <> 0 OR $6 <> 0 THEN
        INSERT INTO log_score(userid1,old_gold1,new_gold1,
            userid2,old_gold2,new_gold2)
            values($1,old_gold1,new_gold1,$5,old_gold2,new_gold2);
    END IF;
    result := 1;
    RETURN QUERY SELECT result,$1,new_gold1,new_score1,$5,new_gold2,new_score2;
END;
$$ LANGUAGE plpgsql;
